package org.zjframework.traceframe.common.dao.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Oracle��ݿ�����
 * 
 * @author cjh 20130718
 */
public class DBconn {

	private Connection conn = null;
	private Statement stmt = null;
	private ResultSet rs = null;
	private PreparedStatement ps =null;

	private final static String DB_URL = "jdbc:mysql://localhost:3306/dstouch";

	private final static String DB_DRIVER = "com.mysql.jdbc.Driver";

	private final static String DB_USERNAME = "root";

	private final static String DB_PASSWORD = "root";

	/**
	 * 
	 * @return
	 */
	public Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName(DB_DRIVER);
			conn = DriverManager
					.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
			stmt = conn.createStatement();
			System.out.println("");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 保存图片到数据库
	 * @param imagePath
	 * @param id
	 * @throws java.sql.SQLException
	 */
	public  void saveBlobToData(String imagePath,String id) throws SQLException{
		//连接MySQl数据库
		conn = this.getConnection();
		PreparedStatement ps=null;
		InputStream in=null; 
		try {
			//从本地硬盘读取一张读片
			in=new FileInputStream(imagePath);
			ps=conn.prepareStatement("update staffinfo set image1=?,staff_txcj_status=1 where id='"+id+"'");
			ps.setBinaryStream(1, in, in.available());
			ps.executeUpdate();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
	
	/**
	 * 保存指纹图片到数据库
	 * @param imagePath
	 * @param id
	 * @throws java.sql.SQLException
	 */
	public  void saveFingerMarkToDB(String sql) throws SQLException{
		//连接MySQl数据库
		conn = this.getConnection();
		PreparedStatement ps=null;
		InputStream in=null; 
		try {
			//从本地硬盘读取一张读片
			/*BmpReader.bmpTojpg("C:\\regFinger.bmp", "C:\\regFinger.jpg");*/
			in=new FileInputStream("C:\\regFinger.jpg");
			ps=conn.prepareStatement(sql);
			
			ps.setBinaryStream(1, in, in.available());
			ps.executeUpdate();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 读取图片
	 * @param imagePath
	 * @param id
	 * @throws java.sql.SQLException
	 */
	//读取数据库二进制文件
	public void readerJpg(String id) throws SQLException
	{
		conn = this.getConnection();//自己连接自己的数据库！！！！！！！！
		String sqlString="select image1 from staffinfo where id='"+id+"'";//从数据库中读出要还原文件的二进制码，这里我读的是自己的数据库id为4的文件
		File file=new File("E:\\1.jpg");//本地生成的文件
		if(!file.exists())
		{
			try {
				file.createNewFile();
			} catch (Exception e) {
				e.printStackTrace();
			}
			
		}
		try {
			byte[] Buffer = new byte[4096*5];
			ps = conn.prepareStatement(sqlString);
			rs = ps.executeQuery();
			if(rs.next())
			{
				FileOutputStream outputStream = new FileOutputStream(file);
				InputStream iStream = rs.getBinaryStream("image1");//去字段用getBinaryStream()
				int size=0;
				while((size=iStream.read(Buffer))!=-1)
				{
					for(int i=0 ; i < Buffer.length; i++){
						System.out.print(Integer.toBinaryString(Buffer[i]));
					}
					outputStream.write(Buffer,0,size);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	public static void main(String[] args) {
		DBconn db = new DBconn();
		try {
			db.readerJpg("123ccc");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	


	/**
	 * ��ѯ��ݲ���
	 * 
	 * @return ResultSet
	 */
	public ResultSet executeQuery(String sqlStr) {
		if (sqlStr == null || sqlStr.length() == 0)
			return null;
		try {
			this.getConnection();
			rs = stmt.executeQuery(sqlStr);
			return rs;
		} catch (SQLException ex) {
			ex.printStackTrace();
			return null;
		}

	}
	

	/**
	 * ������ݲ���
	 * 
	 * @return �����Ƿ�ɹ�
	 */
	public boolean executeUpdate(String sqlStr) {

		if (sqlStr == null || sqlStr.length() == 0)
			return false;
		try {
			this.getConnection();
			stmt.executeUpdate(sqlStr);
			return true;
		} catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

	}
	
	public void commit(Connection conn){
		try {
			conn.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void rollback(Connection conn){
		try {
			conn.rollback();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void closeStmt() {
		try {
			if (stmt != null) {
				stmt.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * �ر���ݿ�����
	 * 
	 * @param connect
	 */
	public void closeConnection() {
		try {
			if (conn != null) {
				/** �жϵ�ǰ�������Ӷ������û�б��رվ͵��ùرշ��� */
				if (!conn.isClosed()) {
					conn.close();
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

}